/*
 * @Author: your name
 * @Date: 2021-06-22 10:29:48
 * @LastEditTime: 2021-08-31 14:25:01
 * @LastEditors: Please set LastEditors
 * @Description: In User Settings Edit
 * @FilePath: \hbs\libs\sqlcmd\mysql.ts
 */
const mysql = require("mysql");
export default class Mysql {
    public cli;
    constructor({ host, user, password, database, port }) {
        this.cli = mysql.createPool({ host, user, password, database, port, multipleStatements: true,"acquireTimeout": 1000000,
        "connectTimeout": 30000 });
    }
    public async Query(sql): Promise<any> {
        return new Promise((resolve, reject) => {
            this.cli.getConnection((err, conn) => {
                if (err) {
                    // console.log(err);
                    reject(err);
                } else {
                    conn.query(sql, (qerr, vals, fields) => {
                        //console.log(qerr);
                        //  console.log(fields);
                        //释放连接  
                        this.cli.releaseConnection(conn)
                        //事件驱动回调  
                        resolve(vals);
                    });
                }
            });
        });
    }
    public static ConvertDate(date,ishour=true) {
        var y = date.getFullYear();
        var m = date.getMonth() + 1;
        m = m < 10 ? ('0' + m) : m;
        var d = date.getDate();
        d = d < 10 ? ('0' + d) : d;
        var h = date.getHours();
        h = h < 10 ? ('0' + h) : h;
        var minute = date.getMinutes();
        var second = date.getSeconds();
        minute = minute < 10 ? ('0' + minute) : minute;
        second = second < 10 ? ('0' + second) : second;
        if(!ishour){
            return y + '-' + m + '-' + d + ' ';
        }
        return y + '-' + m + '-' + d + ' ' + h + ':' + minute + ':' + second;
    
    }
    public async Insert(params, table) {
        let keys = '';
        let values = '';
        for (var p in params) {
            if(params[p]!=undefined){
                if(p=="from"){
                    keys += "`"+p+"`" + ',';
                }else{
                    keys += p + ',';
                }
                
                if (params[p] instanceof Date) {
                    params[p] =Mysql.ConvertDate(params[p]);
                };
                if (params[p] instanceof Array){
                    params[p] =JSON.stringify(params[p]);
                }
                values += `'` + params[p] + `'` + ',';
            }
            
        }
        var str = 'INSERT INTO ' + table + '(' + keys.substr(0, (keys.length - 1)) + ')VALUES(' + values.substr(0, (values.length - 1)) + ')';
        return str;
    }
    public async Update(params, table, condition) {
        let key = '';
        for (var p in params) {
            if (params[p] instanceof Date) {
                params[p] =Mysql.ConvertDate(params[p]);
            };
            if (params[p] instanceof Array){
                params[p] =JSON.stringify(params[p]);
            };
            key += p + '=' + `'` + params[p] + `',`;
        };
        key = key.substr(0, (key.length - 1));
        var condi = '';
        for (var q in condition) {
            condi += this.reWhere(q, condition[q]) + ' and ';
        };
        condi = condi.substr(0, (condi.length - 4));
        var str = 'UPDATE ' + table + ' SET ' + key + ' WHERE ' + condi + '';
        return str;
    }
    public reWhere(key, values) {
        var panduan = key.substr(0, 2);
        var fuhao = '';
        switch (panduan) {
            case "D_":
                key = key.substr(2, key.length);
                fuhao = '>=';
                break;
            case "d_":
                key = key.substr(2, key.length);
                fuhao = '>';
                break;
            case "X_":
                key = key.substr(2, key.length);
                fuhao = '<='
                break;
            case "x_":
                key = key.substr(2, key.length);
                fuhao = '<'
                break;
            default:
                fuhao = '='
        };
        return key + ' ' + fuhao + ' "' + values + '"';
    }
    public async Delete(condition, table) {
        var wherestr = '';
        for (var p in condition) {
            wherestr += this.reWhere(p, condition[p]) + ' and ';
        }
        var str = 'DELETE FROM ' + table + ' where ' + wherestr.substr(0, (wherestr.length - 4));
        return str;
    }
    
}